<?php
    require_once 'PHPExcel.class.php';
	function exportexcel($filename,$info){
    
    $objPHPExcel = new PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("ctos")
    ->setLastModifiedBy("ctos")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

    //set width
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(60);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(29);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(26);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(26);
    /*$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(14);*/


    //设置行高度


    // set table header content
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', '排名')
    ->setCellValue('B1', '姓名')
    ->setCellValue('C1', '联系电话')
    ->setCellValue('D1', '地址')
    /*->setCellValue('F1', '上报时间')
    ->setCellValue('G1', '原因')
    ->setCellValue('H1', '上报人工号')
    ->setCellValue('I1', '姓名')
    ->setCellValue('J1', "当前状态")*/;


    for($i=0;$i<count($info);$i++){
        foreach($info[$i] as $key => $value){
            $a[]=$key;
        }
        $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+2), $info[$i][$a[0]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+2), $info[$i][$a[1]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+2), $info[$i][$a[2]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+2), $info[$i][$a[3]]);
      
        /*$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+2), $info[$i][$a[5]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('G'.($i+2), $info[$i][$a[6]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('H'.($i+2), $info[$i][$a[7]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('I'.($i+2), $info[$i][$a[8]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('J'.($i+2), $info[$i][$a[9]]);*/

        //垂直居中

        $objPHPExcel->getActiveSheet()->getStyle('A'.($i+2).':T'.($i+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加边框

        $objPHPExcel->getActiveSheet()->getStyle('A'.($i+2).':T'.($i+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        //设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension($i+2)->setRowHeight(16);
    }


    //  sheet命名
    $objPHPExcel->getActiveSheet()->setTitle("表格");


    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);


    // excel头参数
    header('content-Type:application/vnd.ms-excel;charset=utf-8');
    //header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=".$filename.".xls");  //日期为文件名后缀3
    header('Cache-Control: max-age=0');
    ob_clean();//关键
    flush();//关键

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式
    $objWriter->save('php://output');






}






function exportexceltofile($filename,$info){
    //vendor("PHPExcel.PHPExcel");
    //spl_autoload_register(array('Think','autoload'));
    //Vendor("Classes.PHPExcel");
    //Vendor("PHPExcel.PHPExcel.IOFactory");
    //Vendor("PHPExcel.PHPExcel.Reader.Excel5");
    //import("Vendor.PHPExcel");
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("ctos")
    ->setLastModifiedBy("ctos")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

    //set width
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(60);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(29);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(26);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(26);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(26);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(26);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(26);
    //$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(26);
    /*$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(14);*/


    //设置行高度


    // set table header content
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', 'qq')
    ->setCellValue('B1', 'login_mobile')
    ->setCellValue('C1', 'mobile')
    ->setCellValue('D1', 'status')
    ->setCellValue('E1', 'type')
    ->setCellValue('F1', 'location')
    ->setCellValue('G1', 'email')
    ->setCellValue('H1', 'nickname')
    ->setCellValue('I1', 'member_type')
    ->setCellValue('J1', 'popularity')
    ->setCellValue('K1', 'login_email')
    ->setCellValue('L1', 'product')
    /*->setCellValue('F1', '上报时间')
    ->setCellValue('G1', '原因')
    ->setCellValue('H1', '上报人工号')
    ->setCellValue('I1', '姓名')
    ->setCellValue('J1', "当前状态")*/;


    for($i=0;$i<count($info);$i++){
    	$lastcolumn= count($info) - $i;
    	echo $lastcolumn."columns last \r\n";
        foreach($info[$i] as $key => $value){
            $a[]=$key;
        }
        $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+2), $info[$i][$a[0]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+2), $info[$i][$a[1]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+2), $info[$i][$a[2]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+2), $info[$i][$a[3]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+2), $info[$i][$a[4]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+2), $info[$i][$a[5]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('G'.($i+2), $info[$i][$a[6]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('H'.($i+2), $info[$i][$a[7]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('I'.($i+2), $info[$i][$a[8]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('J'.($i+2), $info[$i][$a[6]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('K'.($i+2), $info[$i][$a[7]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('L'.($i+2), $info[$i][$a[8]]);
        /*$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+2), $info[$i][$a[5]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('G'.($i+2), $info[$i][$a[6]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('H'.($i+2), $info[$i][$a[7]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('I'.($i+2), $info[$i][$a[8]]);
        $objPHPExcel->getActiveSheet(0)->setCellValue('J'.($i+2), $info[$i][$a[9]]);*/

        //垂直居中

        $objPHPExcel->getActiveSheet()->getStyle('A'.($i+2).':T'.($i+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加边框

        $objPHPExcel->getActiveSheet()->getStyle('A'.($i+2).':T'.($i+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        //设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension($i+2)->setRowHeight(16);
    }


    //  sheet命名
    $objPHPExcel->getActiveSheet()->setTitle("表格");


    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);


    // excel头参数
    /*header('content-Type:application/vnd.ms-excel;charset=utf-8');

    header("Content-Disposition: attachment;filename=".$filename.".xls");  
    header('Cache-Control: max-age=0');
    ob_clean();
    flush();*/
    $filename = iconv("UTF-8", "GBK", $filename);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式
    $objWriter->save($filename.".xls");






}






    /**
         +----------------------------------------------------------
         * Export Excel | 2013.08.23
         * Author:HongPing <hongping626@qq.com>
         +----------------------------------------------------------
         * @param $expTitle     string File name
         +----------------------------------------------------------
         * @param $expCellName  array  Column name
         +----------------------------------------------------------
         * @param $expTableData array  Table data
         +----------------------------------------------------------
         */
        function exportExcel($expTitle,$expCellName,$expTableData){
            $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
            $fileName = $_SESSION['loginAccount'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
            $cellNum = count($expCellName);
            $dataNum = count($expTableData);
            vendor("PHPExcel.PHPExcel");
            $objPHPExcel = new PHPExcel();
            $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
            
            $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
            for($i=0;$i<$cellNum;$i++){
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
            } 
              // Miscellaneous glyphs, UTF-8   
            for($i=0;$i<$dataNum;$i++){
              for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
              }             
            }  
            
            header('pragma:public');
            header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
            header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
            $objWriter->save('php://output'); 
            exit;   
        }
         
        /**
         +----------------------------------------------------------
         * Import Excel | 2013.08.23
         * Author:HongPing <hongping626@qq.com>
         +----------------------------------------------------------
         * @param  $file   upload file $_FILES
         +----------------------------------------------------------
         * @return array   array("error","message")
         +----------------------------------------------------------     
         */   
         function importExecl($file){ 
            if(!file_exists($file)){ 
                return array("error"=>0,'message'=>'file not found!');
            } 
            Vendor("PHPExcel.PHPExcel.IOFactory"); 
            $objReader = PHPExcel_IOFactory::createReader('Excel5'); 
            try{
                $PHPReader = $objReader->load($file);
            }catch(Exception $e){}
            if(!isset($PHPReader)) return array("error"=>0,'message'=>'read error!');
            $allWorksheets = $PHPReader->getAllSheets();
            $i = 0;
            foreach($allWorksheets as $objWorksheet){
                $sheetname=$objWorksheet->getTitle();
                $allRow = $objWorksheet->getHighestRow();//how many rows
                $highestColumn = $objWorksheet->getHighestColumn();//how many columns
                $allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
                $array[$i]["Title"] = $sheetname; 
                $array[$i]["Cols"] = $allColumn; 
                $array[$i]["Rows"] = $allRow; 
                $arr = array();
                $isMergeCell = array();
                foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells
                    foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                        $isMergeCell[$cellReference] = true;
                    }
                }
                for($currentRow = 1 ;$currentRow<=$allRow;$currentRow++){ 
                    $row = array(); 
                    for($currentColumn=0;$currentColumn<$allColumn;$currentColumn++){;                
                        $cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                        $afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);
                        $bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);
                        $col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
                        $address = $col.$currentRow;
                        $value = $objWorksheet->getCell($address)->getValue();
                        if(substr($value,0,1)=='='){
                            return array("error"=>0,'message'=>'can not use the formula!');
                            exit;
                        }
                        if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){
                            $cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                            $formatcode=$cellstyleformat->getFormatCode();
                            if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
                                $value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
                            }else{
                                $value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);
                            }                
                        }
                        if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){
                            $temp = $value;
                        }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){
                            $value=$arr[$currentRow-1][$currentColumn];
                        }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){
                            $value=$temp;
                        }
                        $row[$currentColumn] = $value; 
                    } 
                    $arr[$currentRow] = $row; 
                } 
                $array[$i]["Content"] = $arr; 
                $i++;
            } 
            spl_autoload_register(array('Think','autoload'));//must, resolve ThinkPHP and PHPExcel conflicts
            unset($objWorksheet); 
            unset($PHPReader); 
            unset($PHPExcel); 
            unlink($file); 
            return array("error"=>1,"data"=>$array); 
        }




        //导入
            function impUser(){
                if(isset($_FILES["import"]) && ($_FILES["import"]["error"] == 0)){
                    $result = $this->importExecl($_FILES["import"]["tmp_name"]);
                    if($result["error"] == 1){          
                    $execl_data = $result["data"][0]["Content"];
                            foreach($execl_data as $k=>$v){
                                ..这里写你的业务代码..
                            }
                    }
                }
            }

        //导出
            function expUser(){//导出Excel
                $xlsName  = "User";
                $xlsCell  = array(
                    array('id','账号序列'),
                    array('account','登录账户'),
                    array('nickname','账户昵称')
                );
                $xlsModel = M('Post');
                $xlsData  = $xlsModel->Field('id,account,nickname')->select();
                $this->exportExcel($xlsName,$xlsCell,$xlsData);
            }